package com.cmcc.zysoft.infocollection.dao;

import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import com.cmcc.zysoft.infocollection.model.DictData;
import com.cmcc.zysoft.infocollection.model.News;
import com.starit.common.dao.hibernate.HibernateBaseDaoImpl;
import com.starit.common.dao.support.Pagination;


/**
 * @author jin.congshan
 * @date 2013-1-10 上午9:48:27
 */
@Repository
public class NoticePcDao extends HibernateBaseDaoImpl<News, String> {	
	
	@Resource
	private JdbcTemplate jdbcTemplate;
	
	/**
	 * 获取各通知公告条数
	 * @param userId 用户ID
	 * @author jin.congshan
	 * @date 2013-01-08
	 */
    public Map<String, Object> getUserNoticeCount(String userId, String newsType){
    	String sql = 
    			" SELECT * FROM" +
    			" (SELECT COUNT(distinct b.news_id) AS count_not_read FROM tb_b_read_news a, tb_b_news b WHERE b.news_type = ? and a.news_id = b.news_id and a.user_id = ? AND a.read_num = 0) aa CROSS JOIN" +
    			" (SELECT COUNT(distinct a.news_id) AS count_sended FROM tb_b_news a WHERE a.news_type = ? and a.release_man = ? AND a.state = '1') ab CROSS JOIN" +
    			" (SELECT COUNT(distinct a.news_id) AS count_draft FROM tb_b_news a WHERE a.news_type = ? and a.release_man = ? AND a.state = '2') ac";
    	return this.jdbcTemplate.queryForMap(sql, newsType, userId, newsType, userId, newsType, userId);
    }
    
    /**
     * 获取通知公告分页列表数据
     * @param userId 用户ID
     * @param search 查询
     * @param page
     * @param rows
     * @author jin.congshan
     * @date 2013-1-9 下午7:41:02
     */
    @SuppressWarnings({ "unchecked", "rawtypes" })
	public Pagination<?> getNoticePageData(String userId, String code, String search, int page, int rows){
    	int offset = (page-1)*rows;
    	String cond = "";
    	String rows_sql = "";
    	String count_sql = "";
    	String state = "";
    	if("3".equals(code) || "2".equals(code)){
    		if("3".equals(code)){
    			state = "'2'";
    		} else {
    			state = "'1'";
    		}
    		rows_sql = 
    			" select a.news_id as newsId, a.title, a.release_date as releaseDate, a.state, d.employee_name as employeeName" +
    			" from tb_b_news a, tb_c_system_user c left join tb_c_employee d on c.employee_id = d.employee_id where a.news_type = '0' and a.state = "+state+" and a.release_man = c.user_id" +
    			" and a.release_man = '"+userId+"'";
    		count_sql = "select count(*) from tb_b_news a where a.news_type = '0' and a.state = "+state+" and a.release_man = '"+userId+"'";
    	} else {
    		rows_sql = 
    			" select distinct a.news_id as newsId, a.title, a.release_date as releaseDate, a.state, b.read_num as readNum, d.employee_name as employeeName" +
    			" from tb_b_news a left join tb_b_read_news b ON a.news_id = b.news_id AND b.user_id = '"+userId+"'" +
    			"	, tb_c_system_user c left join tb_c_employee d on c.employee_id = d.employee_id" +
    			" where a.news_type = '0' and a.release_man = c.user_id and (a.release_man = '"+userId+"' or b.read_num is not null)";
    		count_sql = "select count(distinct a.news_id) from tb_b_news a LEFT JOIN tb_b_read_news b ON a.news_id = b.news_id AND b.user_id='"+userId+"'" +
    				" where a.news_type = '0' and (a.release_man = '"+userId+"' or b.read_num is not null)";
    	}
    	if(StringUtils.hasText(search)){
    		cond += " and a.title like '%" + search + "%'";
    	}
    	if("1".equals(code)){
    		cond += " and b.read_num = 0";
    	}
    	rows_sql += cond + " order by a.release_date desc limit ?,?";
    	count_sql += cond;
    	List<Map<String,Object>> result = this.jdbcTemplate.queryForList(rows_sql, offset, rows);
		int count = this.jdbcTemplate.queryForInt(count_sql);
		double totalPages = Math.ceil(count * 1D / rows);
		return new Pagination((long)totalPages, offset, rows, count, result);
    }
    
    /**
     * 获取信息反馈分页列表数据
     * @param userId 用户ID
     * @param newsType 信息分类
     * @param code 查询代码
     * @param typeCode 数据字典代码
     * @param search 查询
     * @param page
     * @param rows
     * @author jin.congshan
     * @date 2013-1-3 下午3:41:02
     */
    @SuppressWarnings({ "unchecked", "rawtypes" })
	public Pagination<?> getMarketInfoPageData(String userId, String newsType, String code, String search, int page, int rows){
    	int offset = (page-1)*rows;
    	String rows_sql = "";
    	String count_sql = "";
    	String cond = "";
    	String typeCode = "";
    	if("1".equals(newsType)) {
    		typeCode = "report_type";
    	} else if("2".equals(newsType)) {
    		typeCode = "market_notice_type";
    	}
    	String state = "";
    	if("3".equals(code) || "2".equals(code)){
    		if("3".equals(code)){
    			state = "'2'";
    		} else {
    			state = "'1'";
    		}
    		rows_sql = 
    			" select a.news_id as newsId, a.title, a.release_date as releaseDate, a.state, d.employee_name as employeeName," +
    			" (select e.data_content from tb_c_dict_data e, tb_c_dict_type f where a.news_sort = e.data_code and e.type_id = f.type_id and f.type_code = '"+typeCode+"') as newsSort" +
    			" from tb_b_news a, tb_c_system_user c left join tb_c_employee d on c.employee_id = d.employee_id where a.news_type = '"+newsType+"' and a.state = "+state+" and a.release_man = c.user_id" +
    			" and a.release_man = '"+userId+"'";
    		count_sql = "select count(*) from tb_b_news a where a.news_type = '"+newsType+"' and a.state = "+state+" and a.release_man = '"+userId+"'";
    	} else {
    		rows_sql = 
    			" select distinct a.news_id as newsId, a.title, a.release_date as releaseDate, a.state, b.read_num as readNum, d.employee_name as employeeName," +
    			" (select e.data_content from tb_c_dict_data e, tb_c_dict_type f where a.news_sort = e.data_code and e.type_id = f.type_id and f.type_code = '"+typeCode+"') as newsSort" +
    			" from tb_b_news a, tb_b_read_news b, tb_c_system_user c left join tb_c_employee d on c.employee_id = d.employee_id where a.news_type = '"+newsType+"' and a.news_id = b.news_id and a.release_man = c.user_id" +
    			" and b.user_id = '"+userId+"'";
    		count_sql = "select count(distinct a.news_id) from tb_b_news a, tb_b_read_news b where a.news_type = '"+newsType+"' and a.news_id = b.news_id and b.user_id = '"+userId+"'";
    	}
    	if(StringUtils.hasText(search)){
    		cond += " and a.title like '%" + search + "%'";
    	}
    	if("1".equals(code)){
    		cond += " and b.read_num = 0";
    	}
    	rows_sql += cond + " order by a.release_date desc limit ?,?";
    	count_sql += cond;
    	List<Map<String,Object>> result = this.jdbcTemplate.queryForList(rows_sql, offset, rows);
		int count = this.jdbcTemplate.queryForInt(count_sql);
		double totalPages = Math.ceil(count * 1D / rows);
		return new Pagination((long)totalPages, offset, rows, count, result);
    }
    
    /**
     * 
     * @Description : 按状态检查通知公告数量
     * @author jin.congshan
     * @date 2013-1-10 下午9:32:01
     * @param state 状态
     * @param ids 通知公告ID字符串
     * @return
     *
     */
    public int checkNoticeState(String state, String ids){
    	String sql = "select count(*) from tb_b_news a where a.state = ? and a.news_id in(" + ids + ")";
    	return this.jdbcTemplate.queryForInt(sql, state);
    }
    
    /**
     * 
     * @Description : 获取指定ID组的通知公告名称集合
     * @author jin.congshan
     * @date 2013-1-10 下午9:36:16
     * @param ids 通知公告ID字符串
     * @return
     *
     */
    public String getNoticeNameStr(String ids, String state){
    	String sql = "select GROUP_CONCAT(a.title) from tb_b_news a where a.news_id in(" + ids + ") and a.state <> ?";
    	return this.jdbcTemplate.queryForObject(sql, String.class, state);
    }
    
    /**
     * 
     * @Description : 删除指定ID组的通知公告
     * @author jin.congshan
     * @date 2013-1-10 下午9:49:03
     * @param ids 通知公告ID字符串
     *
     */
    public void deleteNotice(String ids){
    	String sql = "delete a from tb_b_news a where a.news_id in(" + ids + ")";
    	this.jdbcTemplate.execute(sql);
    }
    
    /**
     * 
     * @Description ：检查通知公告标题是否已经存在
     * @author jin.congshan
     * @date 2013-1-13 下午2:28:49
     * @param newsId 通知公告ID
     * @param companyId 公司ID
     * @param title 标题
     * @param newsType 通知公告类型
     * @return
     *
     */
    public int checkNoticeName(String newsId, String companyId, String title, String newsType) {
    	if(StringUtils.hasText(newsId)){
    		String sql = "select count(*) from tb_b_news a where a.company_id = ? and a.title = ? and a.news_type = ? and a.news_id <> ?";
    		return this.jdbcTemplate.queryForInt(sql, companyId, title, newsType, newsId);
    	} else {
    		String sql = "select count(*) from tb_b_news a where a.company_id = ? and a.title = ? and a.news_type = ?";
    		return this.jdbcTemplate.queryForInt(sql, companyId, title, newsType);
    	}
    }
    
    /**
	 * 
	 * @Description ：根据编码获取数据字典列表
	 * @author jin.congshan
	 * @date 2013-1-13 下午3:10:56
	 * @return
	 *
	 */
    @SuppressWarnings("unchecked")
	public List<DictData> getDictDataByTypeCode(String typeCode){
		String hql = "select a from DictData a, DictType b where a.dictType.typeId = b.typeId and b.typeCode = ?";
		return this.findByHQL(hql, typeCode);
	}
    
    /**
     * 
     * @Description ：获取用户分页列表数据
     * @author jin.congshan
     * @date 2013-1-13 下午6:11:21
     * @param companyId 公司ID
     * @param page 页码
     * @param rows 页面大小
     * @param deptId 部门ID
     * @param keyword 关键字
     * @return
     *
     */
    @SuppressWarnings({ "unchecked", "rawtypes" })
	public Pagination<Object> userPageData(String companyId, String deptId, String keyword, int page, int rows){
    	int offset = (page-1)*rows;
    	String sql_rows = 
    		" select a.user_id, b.employee_name, b.employee_code, b.mobile, b.id_card" +
    		" from tb_c_system_user a LEFT JOIN tb_c_employee b ON a.employee_id = b.employee_id" +
    		" where a.company_id = '" + companyId + "' and a.del_flag = '0'";
    	String sql_count = "select count(*) from tb_c_system_user a LEFT JOIN tb_c_employee b ON a.employee_id = b.employee_id where a.company_id = '" + companyId + "' and a.del_flag = '0'";
    	String cond = "";
    	if(StringUtils.hasText(deptId)){
    		cond += " and b.department_id = '"+deptId+"'";
    	}
    	if(StringUtils.hasText(keyword)){
    		cond += " and (b.employee_name like '%"+keyword+"%' or b.mobile like '%"+keyword+"%')";
    	}
    	sql_rows += cond + " limit ?,?";
    	sql_count += cond;
    	List<Map<String,Object>> result = this.jdbcTemplate.queryForList(sql_rows, offset, rows);
		int count = this.jdbcTemplate.queryForInt(sql_count);
		double totalPages = Math.ceil(count * 1D / rows);
		return new Pagination((long)totalPages, offset, rows, count, result);
    }
    
    /**
     * 
     * @Description ：获取信息详情
     * @author jin.congshan
     * @date 2013-1-13 下午8:32:05
     * @param newsId 信息ID
     * @return
     *
     */
    public Map<String, Object> getNewsDetailInfo(String newsId, String userId){
    	StringBuffer sb = new StringBuffer();
    	sb.append(" select a.news_id as newsId, a.title, a.release_man as releaseMan, a.release_date as releaseDate, a.content, a.state,")
    	  .append("		a.news_type as newsType, a.news_sort as newsSort, a.company_id as companyId, d.employee_name as releaseManName,")
    	  .append("		CASE a.news_type")
    	  .append("			WHEN '0' THEN ''")
    	  .append("			WHEN '1' THEN")
    	  .append("			(")
    	  .append("				SELECT e.data_content FROM tb_c_dict_data e, tb_c_dict_type f")
    	  .append("				WHERE a.news_sort = e.data_code AND e.type_id = f.type_id AND f.type_code = 'report_type'")
    	  .append("			)")
    	  .append("			WHEN '2' THEN")
    	  .append("			(")
    	  .append("				SELECT e.data_content FROM tb_c_dict_data e, tb_c_dict_type f")
    	  .append("				WHERE a.news_sort = e.data_code AND e.type_id = f.type_id AND f.type_code = 'market_notice_type'")
    	  .append("			)")
    	  .append("		END AS newsSortName")
    	  .append("	from tb_b_news a, tb_b_read_news b, tb_c_system_user c left join tb_c_employee d on c.employee_id = d.employee_id")
    	  .append("	where a.news_id = ?")
    	  .append("		and a.news_id = b.news_id")
    	  .append("		and b.user_id = ?")
    	  .append("		and a.release_man = c.user_id");
    	return this.jdbcTemplate.queryForMap(sb.toString(), newsId, userId);
    }
    
    /**
	 * 查找市场信息反馈的图片
	 * @param newsId
	 * @return
	 */
	public List<Map<String,Object>> getImgs(String newsId){
		String sql = "SELECT image.image_addr FROM tb_b_news_images image,tb_b_news news WHERE news.news_id=image.news_id AND news.news_id=? ";
		return this.jdbcTemplate.queryForList(sql,newsId);
	}
}
